package jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 执行关联查询
 */
public class JDBCDemo5 {
    public static void main(String[] args) {
        //查看1年级1班所有学生信息和班级信息
        try (Connection connection = DBUtil.getConnection();){
            Statement statement = connection.createStatement();
//            String sql = "SELECT s.id,s.name,s.job,c.name,c.floor " +
//                         "FROM student s,class c " +
//                         "WHERE s.class_id=c.id " +
//                         "AND c.name='1年级1班'";
            String sql = "SELECT s.id,s.name,s.job,c.name,c.floor " +
                         "FROM student s " +
                         "JOIN class c ON s.class_id=c.id " +
                         "WHERE c.name='1年级1班'";
            ResultSet resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                int sId = resultSet.getInt("s.id");
                String sName = resultSet.getString("s.name");
                String sJob = resultSet.getString("s.job");
                String cName = resultSet.getString("c.name");
                int cFloor = resultSet.getInt("c.floor");
                System.out.println(sId + "," + sName + "," + sJob + "," + cName + "," + cFloor);

            }




        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
